/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package processor;

import entity.Category;
import entity.Complaint;
import entity.Department;
import entity.Members;
import java.util.Date;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import report.DepartmentReport;
import report.Detailreport;
import report.StatusReport;
import report.Summaryreport;

/**
 *
 * @author maynoigi
 */
@Stateless
public class ComplaintFacade extends AbstractFacade<Complaint> {

    @PersistenceContext(unitName = "ManTechHelpdesk-ejbPU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public ComplaintFacade() {
        super(Complaint.class);
    }

    public List<Complaint> getComplaintAll() {
        return em.createNamedQuery("Complaint.findAll").getResultList();
    }

    public List<Complaint> getComplaintByCategory(Category coID) {
        return em.createQuery("SELECT c FROM Complaint c WHERE c.categoryID = :cId ORDER BY c.complaintID DESC ").setParameter("cId", coID).getResultList();
    }

    public List<Complaint> getComplaintByStatus(int status) {
        return em.createQuery("SELECT c FROM Complaint c WHERE c.complaintStatus = :cId ORDER BY c.complaintID DESC ").setParameter("cId", status).getResultList();
    }

    public List<Complaint> getComplaintByStatusAndMember(int status,Members member) {
        return em.createQuery("SELECT c FROM Complaint c WHERE c.complaintStatus = :cId and c.complaintTechnical = :member ORDER BY c.complaintID DESC ").setParameter("cId", status).setParameter("member", member).getResultList();
    }

    public List<Complaint> getComplaintByDepartments(Department dId) {
        return em.createQuery("SELECT c FROM Complaint c WHERE c.departmentID = :dId ORDER BY c.complaintID DESC").setParameter("dId", dId).getResultList();
    }

    public List<Complaint> getComplaintByTechnical(Members member) {
        return em.createQuery("SELECT c FROM Complaint c WHERE c.complaintTechnical = :dId ORDER BY c.complaintID DESC").setParameter("dId", member).getResultList();
    }

    public Complaint getComplaintByComplaintsID(Integer dId) {
        return (Complaint) em.createNamedQuery("Complaint.findByComplaintID").setParameter("complaintID", dId).getSingleResult();
    }

    public List<Complaint> getComplaintByMembers(Members mId) {
        return em.createQuery("SELECT c FROM Complaint c WHERE c.membersID = :mId ORDER BY c.complaintID DESC").setParameter("mId", mId).getResultList();
    }

    public List<Summaryreport> summaryReport() {
        List<Summaryreport> temp = em.createQuery("SELECT new report.Summaryreport(c.departmentID,count(c.departmentID)) FROM Complaint c group by c.departmentID").getResultList();

        for (int i = 0; i < temp.size(); i++) {
            Summaryreport tempWait = new Summaryreport();
            Summaryreport tempDone = new Summaryreport();
            try {
                tempWait = (Summaryreport) em.createQuery("SELECT new report.Summaryreport(count(c.complaintStatus)) FROM Complaint c WHERE c.complaintStatus =0 and c.departmentID = :dpID group by c.departmentID ").setParameter("dpID", temp.get(i).getDepartment()).getSingleResult();
            } catch (NoResultException ex) {
                tempWait.setTotalWait(new Long(0));
            }
            try {
                tempDone = (Summaryreport) em.createQuery("SELECT new report.Summaryreport(count(c.complaintStatus),count(c.complaintStatus)) FROM Complaint c WHERE c.complaintStatus =1 and c.departmentID = :dpID group by c.departmentID ").setParameter("dpID", temp.get(i).getDepartment()).getSingleResult();
            } catch (NoResultException ex) {
                tempDone.setTotalDone(new Long(0));
            }

            temp.get(i).setTotalDone(tempDone.getTotalDone());
            temp.get(i).setTotalWait(tempWait.getTotalWait());
        }
        return temp;
    }

    public List<Summaryreport> summaryReportByDaily(Date daily) {
        Date from = DateConvert.dateFrom(daily);
        Date to = DateConvert.dateTo(daily);

        List<Summaryreport> temp = em.createQuery("SELECT new report.Summaryreport(c.departmentID,count(c.departmentID)) FROM Complaint c where (c.complaintCreateDate between :form and :to) group by c.departmentID").setParameter("form", from).setParameter("to", to).getResultList();

        for (int i = 0; i < temp.size(); i++) {
            Summaryreport tempWait = new Summaryreport();
            Summaryreport tempDone = new Summaryreport();
            try {
                tempWait = (Summaryreport) em.createQuery("SELECT new report.Summaryreport(count(c.complaintStatus)) FROM Complaint c WHERE c.complaintStatus =0 and c.departmentID = :dpID and  (c.complaintCreateDate between :form and :to) group by c.departmentID ").setParameter("dpID", temp.get(i).getDepartment()).setParameter("form", from).setParameter("to", to).getSingleResult();
            } catch (NoResultException ex) {
                tempWait.setTotalWait(new Long(0));
            }
            try {
                tempDone = (Summaryreport) em.createQuery("SELECT new report.Summaryreport(count(c.complaintStatus),count(c.complaintStatus)) FROM Complaint c WHERE  c.complaintStatus =1 and c.departmentID = :dpID and (c.complaintCreateDate between :form and :to) group by c.departmentID ").setParameter("dpID", temp.get(i).getDepartment()).setParameter("form", from).setParameter("to", to).getSingleResult();
            } catch (NoResultException ex) {
                tempDone.setTotalDone(new Long(0));
            }

            temp.get(i).setTotalDone(tempDone.getTotalDone());
            temp.get(i).setTotalWait(tempWait.getTotalWait());
        }
        return temp;
    }

    public List<Summaryreport> summaryReportByWeekly(Date from1, Date to1) {
        Date from = DateConvert.dateFrom(from1);
        Date to = DateConvert.dateTo(to1);

        List<Summaryreport> temp = em.createQuery("SELECT new report.Summaryreport(c.departmentID,count(c.departmentID)) FROM Complaint c where (c.complaintCreateDate between :form and :to) group by c.departmentID").setParameter("form", from).setParameter("to", to).getResultList();

        for (int i = 0; i < temp.size(); i++) {
            Summaryreport tempWait = new Summaryreport();
            Summaryreport tempDone = new Summaryreport();
            try {
                tempWait = (Summaryreport) em.createQuery("SELECT new report.Summaryreport(count(c.complaintStatus)) FROM Complaint c WHERE c.complaintStatus =0 and c.departmentID = :dpID and  (c.complaintCreateDate between :form and :to) group by c.departmentID ").setParameter("dpID", temp.get(i).getDepartment()).setParameter("form", from).setParameter("to", to).getSingleResult();
            } catch (NoResultException ex) {
                tempWait.setTotalWait(new Long(0));
            }
            try {
                tempDone = (Summaryreport) em.createQuery("SELECT new report.Summaryreport(count(c.complaintStatus),count(c.complaintStatus)) FROM Complaint c WHERE  c.complaintStatus =1 and c.departmentID = :dpID and (c.complaintCreateDate between :form and :to) group by c.departmentID ").setParameter("dpID", temp.get(i).getDepartment()).setParameter("form", from).setParameter("to", to).getSingleResult();
            } catch (NoResultException ex) {
                tempDone.setTotalDone(new Long(0));
            }

            temp.get(i).setTotalDone(tempDone.getTotalDone());
            temp.get(i).setTotalWait(tempWait.getTotalWait());
        }
        return temp;
    }

    public List<Detailreport> detailReport() {
        List<Detailreport> temp = em.createQuery("SELECT new report.Detailreport(c.complaintID,c.categoryID,c.complaintCreateDate,c.complaintCloseDate,c.membersID,s.membersID,c.complaintTitle) FROM Complaint c LEFT JOIN c.solutionCollection s").getResultList();


        return temp;
    }

    public List<Detailreport> detailReportDaily(Date daily) {
        Date from = DateConvert.dateFrom(daily);
        Date to = DateConvert.dateTo(daily);
        List<Detailreport> temp = em.createQuery("SELECT new report.Detailreport(c.complaintID,c.categoryID,c.complaintCreateDate,c.complaintCloseDate,c.membersID,s.membersID,c.complaintTitle) FROM Complaint c LEFT JOIN c.solutionCollection s where (c.complaintCreateDate between :form and :to)").setParameter("form", from).setParameter("to", to).getResultList();


        return temp;
    }

    public List<Detailreport> detailReportRange(Date from1, Date to1) {
        Date from = DateConvert.dateFrom(from1);
        Date to = DateConvert.dateTo(to1);
        List<Detailreport> temp = em.createQuery("SELECT new report.Detailreport(c.complaintID,c.categoryID,c.complaintCreateDate,c.complaintCloseDate,c.membersID,s.membersID,c.complaintTitle) FROM Complaint c LEFT JOIN c.solutionCollection s where (c.complaintCreateDate between :form and :to)").setParameter("form", from).setParameter("to", to).getResultList();


        return temp;
    }

    public List<DepartmentReport> DepartmentReport(Department department) {
        List<DepartmentReport> temp = em.createQuery("SELECT new report.DepartmentReport(c.categoryID,c.complaintCreateDate,c.complaintCloseDate,c.membersID,s.membersID,c.complaintTitle) FROM Complaint c LEFT JOIN c.solutionCollection s where c.departmentID = :depart").setParameter("depart", department).getResultList();

        return temp;
    }

    public List<StatusReport> StatusReport() {
        List<StatusReport> temp = em.createQuery("SELECT new report.StatusReport(c.complaintCreateDate,c.complaintCloseDate,c.complaintPriority,c.complaintTitle,c.membersID,s.membersID) FROM Complaint c LEFT JOIN c.solutionCollection s where c.complaintStatus=1 ORDER BY c.complaintID DESC").getResultList();


        return temp;
    }

    public List<StatusReport> StatusReportDaily(Date daily) {
        Date from = DateConvert.dateFrom(daily);
        Date to = DateConvert.dateTo(daily);
        List<StatusReport> temp = em.createQuery("SELECT new report.StatusReport(c.complaintCreateDate,c.complaintCloseDate,c.complaintPriority,c.complaintTitle,c.membersID,s.membersID) FROM Complaint c LEFT JOIN c.solutionCollection s where c.complaintStatus=1 and (c.complaintCreateDate between :form and :to) ORDER BY c.complaintID DESC").setParameter("form", from).setParameter("to", to).getResultList();


        return temp;
    }

    public List<StatusReport> StatusReportRange(Date from1, Date to1) {
        Date from = DateConvert.dateFrom(from1);
        Date to = DateConvert.dateTo(to1);
        List<StatusReport> temp = em.createQuery("SELECT new report.StatusReport(c.complaintCreateDate,c.complaintCloseDate,c.complaintPriority,c.complaintTitle,c.membersID,s.membersID) FROM Complaint c LEFT JOIN c.solutionCollection s where c.complaintStatus=1 and (c.complaintCreateDate between :form and :to) ORDER BY c.complaintID DESC").setParameter("form", from).setParameter("to", to).getResultList();


        return temp;
    }

    public void deleteComplaints(Complaint complaint) {

        em.remove((Complaint) em.createNamedQuery("Complaint.findByComplaintID").setParameter("complaintID", complaint.getComplaintID()).getSingleResult());
    }
}
